
package com.scalar.chiptrack.operations.packingslip.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.packingslip.LotQtyPullAudit;
import com.scalar.chiptrack.operations.packingslip.LotQtyPullAuditList;
import com.scalar.chiptrack.utils.SqlQueryMappings;


public class LotQtyPullAuditDAO {
    
    public static LotQtyPullAuditList loadLotQtyPullAuditList( String lotNumber ) throws ScalarException 
    {       
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        LotQtyPullAuditList lotQtyPullAuditList = new LotQtyPullAuditList();

        if ( lotNumber == null ) {
            return lotQtyPullAuditList;
        }

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall( SqlQueryMappings.get ("operations.packingslip.lotQtyPullAudit.loadLotQtyPullAuditList") );
            pstmt.setString ( 1, lotNumber );
            
            rset = pstmt.executeQuery();    
            
            LotQtyPullAudit lotQtyPullAudit = null;
                            
            while (rset.next()) {
                lotQtyPullAudit = new LotQtyPullAudit( rset.getString ("SalesOrder_No"),
                                                       rset.getInt ("LineItem_No"),
                                                       rset.getString ("PS_Label"), 
                                                       rset.getString ("Lot_Number"),
                                                       rset.getInt ("Qty_Pulled"),
                                                       rset.getString ("Qty_Pulled_By"),
                                                       rset.getString ("Qty_Pulled_On"),
                                                       rset.getString ("Part_Number"));

                lotQtyPullAuditList.add (lotQtyPullAudit);
            }
        }
        
        catch( SQLException se ) 
        {
            //se.printStackTrace();
            if ( se.getMessage().indexOf( "No ResultSet" ) == -1 )
            {
                throw new ScalarException( "Could not retrieve Lot Qty Pull Audit list from database", se );
            }
            
            System.out.println( "No records found for lotNumber: " + lotNumber );
        }
        
        catch( Exception e ) 
        {
            e.printStackTrace();
            throw new ScalarException ("Could not retrieve lotQtyPullAudit list from database", e);
        }
        
        finally  
        {
            if( rset != null )
            {
                try { rset.close(); } 
                catch ( SQLException ignore ) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } 
                catch ( SQLException ignore ) {}
            }
            if( conn != null )
            {
                try { conn.close(); } 
                catch ( SQLException ignore ) {}
            }
        }

        return lotQtyPullAuditList;
    }

    // added by hareesh on 18th August 2003
    // -- START --
    // Override LotQtyPullAuditList accordingly to the LotId instead of LotNumber.
    // Since there may exist duplicate Lot Number.
    public static LotQtyPullAuditList loadLotQtyPullAuditList(int lotId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        LotQtyPullAuditList lotQtyPullAuditList = new LotQtyPullAuditList();

        if ( lotId == -1 ) {
            return lotQtyPullAuditList;
        }

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall( SqlQueryMappings.get ("operations.packingslip.lotQtyPullAudit.loadLotQtyPullAuditListByLotId") );
            pstmt.setInt ( 1, lotId);

            rset = pstmt.executeQuery();

            LotQtyPullAudit lotQtyPullAudit = null;

            while (rset.next()) {
                lotQtyPullAudit = new LotQtyPullAudit( rset.getString ("SalesOrder_No"),
                                                       rset.getInt ("LineItem_No"),
                                                       rset.getString ("PS_Label"),
                                                       rset.getString ("Lot_Number"),
                                                       rset.getInt ("Qty_Pulled"),
                                                       rset.getString ("Qty_Pulled_By"),
                                                       rset.getString ("Qty_Pulled_On"),
                                                       rset.getString ("Part_Number"));

                lotQtyPullAuditList.add (lotQtyPullAudit);
            }
        }

        catch( SQLException se )
        {
            //se.printStackTrace();
            if ( se.getMessage().indexOf( "No ResultSet" ) == -1 )
            {
                throw new ScalarException( "Could not retrieve Lot Qty Pull Audit list from database", se );
            }

            System.out.println( "No records found for lotId: " + lotId );
        }

        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ("Could not retrieve lotQtyPullAudit list from database", e);
        }

        finally
        {
            if( rset != null )
            {
                try { rset.close(); }
                catch ( SQLException ignore ) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); }
                catch ( SQLException ignore ) {}
            }
            if( conn != null )
            {
                try { conn.close(); }
                catch ( SQLException ignore ) {}
            }
        }

        return lotQtyPullAuditList;
    }
    // -- END --

    public static void main(String agrs[]) throws Exception{
        System.setProperty("cmd", "");
        System.setProperty("scalar.properties", "c:/chiptrack/config/scalar.properties");

        LotQtyPullAuditList lotQtyPullAuditList = loadLotQtyPullAuditList("HFCYM.00");
        Iterator lotQtyPullAuditListIterator = lotQtyPullAuditList.iterator();
        
        while( lotQtyPullAuditListIterator.hasNext() ) {
            System.out.println( lotQtyPullAuditListIterator.next() );
        }
    }
}